package busterminal.persistence.jdbc;

import busterminal.entity.Route;
import busterminal.entity.RouteImplementation;
import busterminal.entity.StoppingPoint;
import busterminal.entity.StoppingPointImplementation;
import busterminal.entity.service.RouteType;
import busterminal.exception.BusinessException;
import busterminal.exception.DatabaseException;
import busterminal.persistence.RouteDAO;
import busterminal.persistence.jdbc.proxy.RouteProxy;
import busterminal.persistence.jdbc.proxy.StoppingPointProxy;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Реализация интерфейса Data Access Object для маршрута
 */
public class JdbcRouteDAO extends AbstractJdbcDAO<Route> implements RouteDAO {

    /**
     * Добавить маршрут в базу данных
     * @param route ссылка на маршрут
     */
    @Override
    public void create(Route route) {
        route.setId(getNextId());

        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("INSERT INTO Route (Id, RouteNumber, RouteName, StartPointId, EndPointId, RouteType) VALUES (?, ?, ?, ?, ?, ?)");
            st.setInt(1, route.getId());
            st.setString(2, route.getRouteNumber());
            st.setString(3, route.getRouteName());
            st.setInt(4, route.getStartPoint().getId());
            st.setInt(5, route.getEndPoint().getId());
            st.setString(6, route.getRouteType().toString());
            st.execute();

            st = cn.prepareStatement("INSERT INTO IntermediateStoppingPoints (RouteId, StoppingPointId) VALUES (?, ?)");
            int routeId = route.getId();
            for (StoppingPoint stoppingPoint : route.getIntermediateStoppingPointsList()) {
                st.setInt(1, routeId);
                st.setInt(2, stoppingPoint.getId());
                st.execute();
            }

        } catch (SQLException e) {
            throw new DatabaseException("Cann't create new route with route name " + route.getRouteName(), e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Прочитать маршрут из базы данных
     * @param id идентификатор маршрута
     * @return ссылка на маршрут
     */
    @Override
    public Route read(Integer id) {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        Route route = null;
        try {
            st = cn.createStatement();
            rs = st.executeQuery("SELECT Id, RouteNumber, RouteName, StartPointId, EndPointId, RouteType "
                    + "FROM Route "
                    + "WHERE Id=" + id);
            if (rs.next()) {
                route = new RouteImplementation();
                route.setId(rs.getInt("Id"));
                route.setRouteNumber(rs.getString("RouteNumber").trim());
                route.setRouteName(rs.getString("RouteName".trim()));
                StoppingPoint startStoppingPoint = new StoppingPointProxy();
                startStoppingPoint.setId(rs.getInt("StartPointId"));
                route.setStartPoint(startStoppingPoint);
                StoppingPoint endStoppingPoint = new StoppingPointProxy();
                endStoppingPoint.setId(rs.getInt("EndPointId"));
                route.setEndPoint(endStoppingPoint);
                route.setRouteType(RouteType.valueOf(rs.getString("RouteType").trim()));

                rs = st.executeQuery("SELECT RouteId, StoppingPointId FROM IntermediateStoppingPoints WHERE RouteId=" + route.getId());
                ArrayList<StoppingPoint> intermediateStoppingPointsList = new ArrayList<StoppingPoint>();
                while (rs.next()) {
                    StoppingPoint stoppingPoint = new StoppingPointProxy();
                    stoppingPoint.setId(rs.getInt("StoppingPointId"));
                    intermediateStoppingPointsList.add(stoppingPoint);
                }
                route.setIntermediateStoppingPointsList(intermediateStoppingPointsList);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cann't read route with Id=" + id, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (route == null) {
            throw new BusinessException("Route with Id=" + id + " not found");
        }
        return route;
    }

    /**
     * Обновить сведения о маршруте в базе данных
     * @param route ссылка на маршрут
     */
    @Override
    public void update(Route route) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        try {
            st = cn.prepareStatement("UPDATE Route SET RouteNumber=?, RouteName=?, StartPointId=?, EndPointId=?, RouteType=? WHERE Id=?");
            st.setString(1, route.getRouteNumber());
            st.setString(2, route.getRouteName());
            st.setInt(3, route.getStartPoint().getId());
            st.setInt(4, route.getEndPoint().getId());
            st.setString(5, route.getRouteType().toString());
            st.setInt(6, route.getId());
            st.execute();
            st = cn.prepareStatement("DELETE FROM IntermediateStoppingPoints WHERE RouteId=?");
            st.setInt(1, route.getId());
            st.execute();
            st = cn.prepareStatement("INSERT INTO IntermediateStoppingPoints (RouteId, StoppingPointId) VALUES (?, ?)");
            int routeId = route.getId();
            for (StoppingPoint sp : route.getIntermediateStoppingPointsList()) {
                st.setInt(1, routeId);
                st.setInt(2, sp.getId());
                st.execute();
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cann't update route with Id=" + route.getId(), e);
        } finally {
            try {
                st.close();
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Удалить маршрут из базы данных
     * @param route ссылка на маршрут
     */
    @Override
    public void delete(Route route) {
        delete(route.getId());
    }

    /**
     * Удалить маршрут из базы данных
     * @param id идентификатор модели автобуса
     */
    @Override
    public void delete(Integer id) {
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement("DELETE FROM IntermediateStoppingPoints WHERE RouteId=?");
            preparedStatement.setInt(1, id);
            preparedStatement.execute();
            
            preparedStatement = connection.prepareStatement("DELETE FROM Route WHERE Id=?");
            preparedStatement.setInt(1, id);
            preparedStatement.execute();
        } catch (SQLException e) {
            throw new DatabaseException("Cannot delete route with Id=" + id, e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cannot close statement");
                ex.printStackTrace(System.err);
            }
        }
    }

    /**
     * Получить список всех маршрутов из базы данных
     * @return список маршрутов
     */
    @Override
    public List<Route> findAll() {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        List<Route> routeList = new ArrayList<Route>();
        try {
            st = cn.createStatement();
            rs = st.executeQuery("SELECT Id FROM Route");
            while (rs.next()) {
                Route route = new RouteProxy();
                route.setId(rs.getInt("Id"));
                routeList.add(route);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cann't read bus list", e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (routeList == null) {
            throw new BusinessException("Route list is empty");
        }
        return routeList;
    }

    /**
     * Найти маршрут по названию маршрута
     * @param routeName название маршрута
     * @return ссылка на маршрут
     */
    @Override
    public List<Route> findByRouteName(String routeName) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        List<Route> routeList = new ArrayList<Route>();
        try {
            st = cn.prepareStatement("SELECT Id, RouteNumber, RouteName, StartPointId, EndPointId, RouteType "
                    + "FROM Route "
                    + "WHERE RouteName=?");
            st.setString(1, routeName);
            rs = st.executeQuery();
            if (rs.next()) {
                Route route = new RouteImplementation();
                route.setId(rs.getInt("Id"));
                route.setRouteNumber(rs.getString("RouteNumber".trim()));
                route.setRouteName(rs.getString("RouteName".trim()));
                StoppingPoint startStoppingPoint = new StoppingPointProxy();
                startStoppingPoint.setId(rs.getInt("StartPointId"));
                route.setStartPoint(startStoppingPoint);
                StoppingPoint endStoppingPoint = new StoppingPointProxy();
                endStoppingPoint.setId(rs.getInt("EndPointId"));
                route.setEndPoint(endStoppingPoint);
                route.setRouteType(RouteType.valueOf(rs.getString("RouteType").trim()));

                st = cn.prepareStatement("SELECT RouteId, StoppingPointId FROM IntermediateStoppingPoints WHERE RouteId=?");
                st.setInt(1, route.getId());
                rs = st.executeQuery();
                ArrayList<StoppingPoint> intermediateStoppingPointsList = new ArrayList<StoppingPoint>();
                while (rs.next()) {
                    StoppingPoint stoppingPoint = new StoppingPointProxy();
                    stoppingPoint.setId(rs.getInt("StoppingPointId"));
                    intermediateStoppingPointsList.add(stoppingPoint);
                }
                route.setIntermediateStoppingPointsList(intermediateStoppingPointsList);

                routeList.add(route);
            }
        } catch (SQLException e) {
            throw new DatabaseException("Cann't read route with route name " + routeName, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (routeList == null) {
            throw new BusinessException("Route with route name " + routeName + " not found");
        }
        return routeList;
    }
    
    @Override
    public List<Route> findByStoppingPointName(String stoppingPointName) {
        Connection cn = getConnection();
        PreparedStatement st = null;
        ResultSet rs = null;
        List<Route> routeList = new ArrayList<Route>();
        try {
            st = cn.prepareStatement("SELECT Id, StoppingPointName FROM StoppingPoint WHERE StoppingPointName=?");
            st.setString(1, stoppingPointName);
            rs = st.executeQuery();
            if (rs.next()) {
                StoppingPoint stoppingPoint = new StoppingPointImplementation();
                stoppingPoint.setId(rs.getInt("Id"));
                stoppingPoint.setStoppingPointName("StoppingPointName".trim());

                // поиск маршрутов по названию конечного остановочного пункта
                st = cn.prepareStatement("SELECT Id, RouteNumber, RouteName, StartPointId, EndPointId, RouteType "
                        + "FROM Route "
                        + "WHERE EndPointId=?");
                st.setInt(1, stoppingPoint.getId());
                rs = st.executeQuery();
                if (rs.next()) {
                    Route route = new RouteImplementation();
                    route.setId(rs.getInt("Id"));
                    route.setRouteNumber(rs.getString("RouteNumber".trim()));
                    route.setRouteName(rs.getString("RouteName".trim()));
                    StoppingPoint startStoppingPoint = new StoppingPointProxy();
                    startStoppingPoint.setId(rs.getInt("StartPointId"));
                    route.setStartPoint(startStoppingPoint);
                    StoppingPoint endStoppingPoint = new StoppingPointProxy();
                    endStoppingPoint.setId(rs.getInt("EndPointId"));
                    route.setEndPoint(endStoppingPoint);
                    route.setRouteType(RouteType.valueOf(rs.getString("RouteType").trim()));

                    st = cn.prepareStatement("SELECT RouteId, StoppingPointId FROM IntermediateStoppingPoints WHERE RouteId=?");
                    st.setInt(1, route.getId());
                    rs = st.executeQuery();
                    ArrayList<StoppingPoint> intermediateStoppingPointsList = new ArrayList<StoppingPoint>();
                    while (rs.next()) {
                        StoppingPoint sp = new StoppingPointProxy();
                        sp.setId(rs.getInt("StoppingPointId"));
                        intermediateStoppingPointsList.add(sp);
                    }
                    route.setIntermediateStoppingPointsList(intermediateStoppingPointsList);

                    routeList.add(route);
                }
                
                // поиск маршрутов по названию промежуточного остановочного пункта
                st = cn.prepareStatement("SELECT RouteId, StoppingPointId FROM IntermediateStoppingPoints WHERE StoppingPointId=?");
                st.setInt(1, stoppingPoint.getId());
                rs = st.executeQuery();
                while (rs.next()) { 
                    Route route = new RouteProxy();
                    route.setId(rs.getInt("RouteId"));
                    routeList.add(route);
                }
            }

        } catch (SQLException e) {
            throw new DatabaseException("Cann't read route with stopping point name " + stoppingPointName, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Cann't close statement");
                ex.printStackTrace(System.err);
            }
        }
        if (routeList == null) {
            throw new BusinessException("Route with stopping point name " + stoppingPointName + " not found");
        }
        return routeList;
    }
    
}
